from math import radians, sin, cos, atan2, sqrt

from sqlalchemy import Column, BigInteger, Text, DateTime, String, SMALLINT, Integer, ForeignKey, Float, func, Enum
from sqlalchemy.orm import relationship, backref, Session

from app.database import Base
from app.schemas.conversations import ConversationStatus


class Friend(Base):
    __tablename__ = "friends"

    id = Column(Integer, primary_key=True)
    uid = Column(BigInteger, ForeignKey("users.id"))
    friend_id = Column(BigInteger, ForeignKey("users.id"))
    status = Column(SMALLINT(), default=0, nullable=False)

    user = relationship("Users", foreign_keys=[uid])
    friend = relationship("Users", foreign_keys=[friend_id])
    created_at = Column(DateTime(), nullable=False)
    updated_at = Column(DateTime(), nullable=False)


class Users(Base):
    __tablename__ = "users"
    id = Column(BigInteger(), primary_key=True, autoincrement=True)
    password = Column(String(64), nullable=False)
    openid = Column(String(32), unique=True, nullable=False)
    name = Column(String(32), nullable=False)
    sex = Column(SMALLINT(), nullable=True)
    age = Column(SMALLINT(), nullable=False)
    education = Column(String(32), nullable=True)
    height = Column(Integer(), nullable=True)
    income = Column(Integer(), nullable=True)
    major = Column(String(60), nullable=True)
    is_house = Column(SMALLINT(), nullable=True)
    strengths = Column(String(60), nullable=True)
    blood_type = Column(String(12), nullable=True)
    is_marry = Column(SMALLINT(), nullable=True)
    other = Column(Text(), nullable=True)
    province = Column(Integer(), nullable=False)
    city = Column(Integer(), nullable=True)
    country = Column(Integer(), nullable=True)
    avatar = Column(String(60), nullable=True)
    images = Column(Text(), nullable=True)
    points = Column(Float(), nullable=False, default=0)
    latitude = Column(Float)
    longitude = Column(Float)
    children = relationship('Posts', back_populates='user', lazy='joined')
    friends = relationship('Friend',
                           foreign_keys=[Friend.friend_id],
                           backref='user_friends', lazy='joined')
    created_at = Column(DateTime(), nullable=False)
    updated_at = Column(DateTime(), nullable=False)

    def obj_to_dict(self):
        return {
            'id': self.id,
            'name': self.name,
            'age': self.age
        }

    def distance_from(self, other_user):
        earth_radius_km = 6371.0
        lat1 = radians(self.latitude)
        lon1 = radians(self.longitude)
        lat2 = radians(other_user.latitude)
        lon2 = radians(other_user.longitude)
        dlon = lon2 - lon1
        dlat = lat2 - lat1
        a = sin(dlat / 2) ** 2 + cos(lat1) * cos(lat2) * sin(dlon / 2) ** 2
        c = 2 * atan2(sqrt(a), sqrt(1 - a))
        distance_km = earth_radius_km * c
        return distance_km

    def nearby_users(self, max_distance_km=10):
        session = Session()
        total = session.query(func.count()).select_from(Users).scalar()
        all_users = session.query(Users).all()
        nearby_users = []
        for user in all_users:
            if user.id != self.id and user.distance_from(self) <= max_distance_km:
                user.distance_km = user.distance_from(self)
                for friend in user.friends:
                    if friend.uid == self.id:
                        user.is_friends = True
                    else:
                        user.is_friends = False
                nearby_users.append(user)
        return {'list': nearby_users, 'total': total}


class UserCondition(Base):
    __tablename__ = "user_condition"
    id = Column(BigInteger(), primary_key=True, autoincrement=True)
    uid = Column(BigInteger(), unique=True, nullable=False)
    city = Column(Integer(), nullable=True)
    age = Column(SMALLINT(), nullable=False)
    height = Column(Integer(), nullable=True)
    education = Column(String(32), nullable=True)
    income = Column(Integer(), nullable=True)
    created_at = Column(DateTime(), nullable=False)
    updated_at = Column(DateTime(), nullable=False)


class Advert(Base):
    __tablename__ = "advert"
    id = Column(BigInteger(), primary_key=True, autoincrement=True)
    pid = Column(BigInteger(), unique=True, nullable=False)
    title = Column(Integer(), nullable=True)
    height = Column(Integer(), nullable=True)
    width = Column(String(32), nullable=True)
    created_at = Column(DateTime(), nullable=False)
    updated_at = Column(DateTime(), nullable=False)


class Music(Base):
    __tablename__ = "music"
    id = Column(BigInteger(), primary_key=True, autoincrement=True)
    avatar = Column(String(60), nullable=True)
    title = Column(String(60), nullable=True)
    like = Column(Integer(), nullable=True)
    watch = Column(Integer(), nullable=True)
    content = Column(Text(), nullable=True)
    price = Column(Integer(), nullable=True)
    children = relationship('MusicList', backref=backref('music', uselist=False))
    created_at = Column(DateTime(), nullable=False)
    updated_at = Column(DateTime(), nullable=False)


class MusicList(Base):
    __tablename__ = "music_list"
    id = Column(BigInteger(), primary_key=True, autoincrement=True)
    pid = Column(BigInteger(), ForeignKey('music.id'))
    title = Column(String(60), nullable=True)
    file_url = Column(Text(), nullable=True)
    parent = relationship('Music', order_by='Music.id', back_populates="children")
    created_at = Column(DateTime(), nullable=False)
    updated_at = Column(DateTime(), nullable=False)


class Points(Base):
    __tablename__ = "user_points"
    id = Column(BigInteger(), primary_key=True, autoincrement=True)
    title = Column(String(60), nullable=True)
    price = Column(Float(), nullable=True, default=0)
    points = Column(Integer, nullable=True, default=0)
    children = relationship('PointsRecord', backref=backref('user_points', uselist=False))
    created_at = Column(DateTime(), nullable=False)
    updated_at = Column(DateTime(), nullable=False)


class PointsRecord(Base):
    __tablename__ = "user_points_record"
    id = Column(BigInteger(), primary_key=True, autoincrement=True)
    pid = Column(BigInteger(), ForeignKey('user_points.id'))
    uid = Column(BigInteger(), ForeignKey('users.id'))
    price = Column(Float(), nullable=True, default=0)
    points = Column(Integer(), nullable=True, default=0)
    parent = relationship('Points', order_by='Points.id', back_populates="children")
    created_at = Column(DateTime(), nullable=False)
    updated_at = Column(DateTime(), nullable=False)


class Gift(Base):
    __tablename__ = "user_gift"
    id = Column(BigInteger(), primary_key=True, autoincrement=True)
    title = Column(String(60), nullable=True)
    price = Column(Integer, nullable=True, default=0)
    image_url = Column(String(255), nullable=True, default=0)
    children = relationship('GiftRecord', backref=backref('`user_gift`', uselist=False))
    created_at = Column(DateTime(), nullable=False)
    updated_at = Column(DateTime(), nullable=False)


class GiftRecord(Base):
    __tablename__ = "user_gift_record"
    id = Column(BigInteger(), primary_key=True, autoincrement=True)
    pid = Column(BigInteger(), ForeignKey('user_gift.id'))
    uid = Column(BigInteger(), ForeignKey('users.id'))
    price = Column(Float(), nullable=True, default=0)
    parent = relationship('Gift', order_by='Gift.id', back_populates="children")
    created_at = Column(DateTime(), nullable=False)
    updated_at = Column(DateTime(), nullable=False)


class Posts(Base):
    __tablename__ = "circle_posts"

    id = Column(Integer, primary_key=True)
    uid = Column(BigInteger, ForeignKey("users.id"))
    content = Column(Text)
    images = Column(String(255))
    likes_count = Column(Integer, default=0)
    comments_count = Column(Integer, default=0)
    user = relationship('Users', back_populates='children', lazy='joined')
    created_at = Column(DateTime)
    updated_at = Column(DateTime(), nullable=False)


class Likes(Base):
    __tablename__ = "circle_likes"

    id = Column(Integer, primary_key=True)
    uid = Column(BigInteger, ForeignKey("users.id"))
    post_id = Column(Integer, ForeignKey("circle_posts.id"))
    created_at = Column(DateTime)
    updated_at = Column(DateTime(), nullable=False)


class Comments(Base):
    __tablename__ = "circle_comments"

    id = Column(Integer, primary_key=True)
    uid = Column(BigInteger, ForeignKey("users.id"))
    post_id = Column(Integer, ForeignKey("circle_posts.id"))
    content = Column(Text)
    created_at = Column(DateTime)
    updated_at = Column(DateTime(), nullable=False)


class LocationHistory(Base):
    __tablename__ = "user_location_history"
    id = Column(Integer, primary_key=True)
    uid = Column(BigInteger, ForeignKey("users.id"))
    latitude = Column(Float)
    longitude = Column(Float)
    created_at = Column(DateTime)
    updated_at = Column(DateTime(), nullable=False)


class AdSlot(Base):
    __tablename__ = 'ad_slot'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    width = Column(SMALLINT)
    height = Column(SMALLINT)
    ads = relationship('Ad', back_populates='ad_slot', lazy='joined')
    created_at = Column(DateTime)
    updated_at = Column(DateTime(), nullable=False)


class Ad(Base):
    __tablename__ = 'ad'
    id = Column(Integer, primary_key=True)
    slot_id = Column(Integer, ForeignKey('ad_slot.id'))
    title = Column(String(50))
    image_url = Column(String(100))
    target_url = Column(String(100))
    created_at = Column(DateTime)
    updated_at = Column(DateTime(), nullable=False)
    status = Column(SMALLINT)
    ad_slot = relationship('AdSlot', back_populates='ads', lazy='joined')


# 聊天记录表
class Message(Base):
    __tablename__ = "conversations_message"

    id = Column(Integer, primary_key=True, index=True)
    conversation_id = Column(Integer, ForeignKey("conversations.id"))
    sender_id = Column(BigInteger, ForeignKey("users.id"))
    receiver_id = Column(BigInteger, ForeignKey("users.id"))
    message = Column(Text)
    type = Column(String(32))

    sender = relationship("Users", foreign_keys=[sender_id])
    receiver = relationship("Users", foreign_keys=[receiver_id])
    # conversation = relationship("Conversation", back_populates="messages")
    conversation = relationship("Conversation", back_populates="messages", foreign_keys=[conversation_id])
    created_at = Column(DateTime(), nullable=False)
    updated_at = Column(DateTime(), nullable=False)

    def obj_to_dict(self):
        return {
            'id': self.id,
            'sender_id': self.sender_id,
            'conversation_id': self.conversation_id,
            'receiver_id': self.receiver_id,
            'message': self.message,
            'type': self.type,
        }


# 对话表
class Conversation(Base):
    __tablename__ = "conversations"
    id = Column(Integer, primary_key=True, index=True)
    creator_id = Column(BigInteger, ForeignKey("users.id"))
    participant_id = Column(BigInteger, ForeignKey("users.id"))
    last_message_id = Column(Integer, ForeignKey("conversations_message.id"))
    unread_messages = Column(Integer, default=0)

    status = Column(SMALLINT, default=1)
    creator = relationship("Users", foreign_keys=[creator_id])
    participant = relationship("Users", foreign_keys=[participant_id], uselist=False)
    last_message = relationship('Message',
                                foreign_keys=[last_message_id],
                                backref='conversations_message', remote_side=[Message.id])
    # messages = relationship("Message", back_populates="conversation")
    messages = relationship(
        "Message",
        back_populates="conversation",
        primaryjoin="Conversation.id == Message.conversation_id",
        remote_side=[Message.conversation_id]
    )

    created_at = Column(DateTime(), nullable=False)
    updated_at = Column(DateTime(), nullable=False)

    def obj_to_dict(self):
        return {
            'id': self.id,
            'unread_messages':self.unread_messages,
            'last_message': self.last_message.obj_to_dict(),
            'creator': self.participant.obj_to_dict()
        }
